Excel BI - Excel Challenge 839

excel-challenges

excel-formulas

🔰 List the unique alphabets in columns.

Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 839

Challenge Description

🔰 List the unique alphabets in columns. Uniqueness should be considered by taking all left columns to the columns being considered + column itself.

Solutions

library(tidyverse)
library(readxl)

path = "Excel/800-899/839/839 List Unique Across Columns.xlsx"
input = read_excel(path, range = "A2:E9", col_names = FALSE)
test  = read_excel(path, range = "G2:K5", col_names = FALSE)  


accumulate_uniques = function(cols) {
  res = reduce(seq_along(cols), function(acc, i) {
    seen = unlist(acc)
    new = setdiff(unique(na.omit(cols[[i]])), seen)
    append(acc, list(new))
  }, .init = list())
  maxlen = max(lengths(res))
  res_padded = purrr::map(res, ~c(.x, rep(NA, maxlen - length(.x))))
  res_padded
}

result =  accumulate_uniques(input) %>% 
  as.data.frame() %>% 
  setNames(., colnames(input))

all.equal(result, test, check.attributes = FALSE)

Logic: Read the workbook ranges needed for the challenge.
Strengths: The code maps the workbook rule into a compact, reproducible pipeline.
Areas for Improvement: The solution assumes the workbook layout and selected ranges remain stable, so any structural change in the sheet would require small adjustments.
Gem: The elegant part is how little code is needed once the correct intermediate representation is chosen.

import pandas as pd
import numpy as np

path = "800-899/839/839 List Unique Across Columns.xlsx"
input = pd.read_excel(path, header=None, usecols="A:E", skiprows=1, nrows=8)
test = pd.read_excel(path, header=None, usecols="G:K", skiprows=1, nrows=4)

def accumulate_uniques(cols):
    res, seen = [], set()
    for col in cols:
        new = [x for x in pd.Series(col).dropna().unique() if x not in seen]
        seen.update(new)
        res.append(new)
    maxlen = max(map(len, res))
    return [r + [np.nan] * (maxlen - len(r)) for r in res]

result_cols = accumulate_uniques([input[col] for col in input.columns])
result = pd.DataFrame(result_cols).transpose()
result.columns = test.columns

print(result.equals(test)) # True

The Python version keeps the algorithm explicit, which helps when the challenge depends on a greedy or iterative rule.

Difficulty Level

Easy / Medium

The business rule is clear, though the workbook still needs a few transformation steps to reach the expected output.